package com.clouds.wms.dao;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.clouds.wms.bean.Purmh;
import com.clouds.wms.exception.DatabaseException;
import com.clouds.wms.util.Function;
import com.clouds.wms.util.PagesBean;

@Repository("purmhDao")
public class PurmhDaoImpl implements PurmhDao {
	private Log log = LogFactory.getLog(getClass());
	@Resource(name="namedParameterJdbcTemplate")
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	@Override
	public PagesBean getPurmhList(String mgaa, String mgab, int offset,int pageSize) throws DatabaseException {
		PagesBean pb = new PagesBean();
		List<Purmh> list = new ArrayList<Purmh>();
		StringBuffer sql = new StringBuffer("SELECT * FROM purmh ");
		sql.append( " where mhaa = '" + Function.formatSQLString(mgaa) + "'");
		sql.append( " and mhab = '" + Function.formatSQLString(mgab) + "'");
		sql.append(" ORDER BY mhad DESC");
		try {
			int totalCount = namedParameterJdbcTemplate.getJdbcOperations().queryForInt(Function.toCountSql(sql.toString()));
			if(totalCount!=0){
				list = namedParameterJdbcTemplate.getJdbcOperations().query(Function.mySqlQuery(sql.toString(),offset,pageSize,totalCount),new BeanPropertyRowMapper<Purmh>(Purmh.class));
			}
			pb.setCount(totalCount);
			pb.setData(list);
			return pb;
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public int getMaxMhac()throws DatabaseException {
		int mhac = namedParameterJdbcTemplate.getJdbcOperations().queryForInt("select IFNULL(max(mhac),0) mhac from purmh ");
		if(mhac==0){
			mhac = 10000;
		}else{
			mhac += 10000;
		}
		return mhac;
	}
	@Override
	public int savePurmh(Purmh purmh) throws DatabaseException {
		String sql = "INSERT INTO purmh (mhaa,mhab,mhac,mhad,mhae,mhaf,mhag,mhah,mhai,mhaj,mhak,mhal,mham) VALUES (:mhaa,:mhab,:mhac,:mhad,:mhae,:mhaf,:mhag,:mhah,:mhai,:mhaj,:mhak,:mhal,:mham)";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(purmh);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public int delPurmh(String mhaa,String mhab,String mhacs) throws DatabaseException {
		String sql = "DELETE FROM purmh WHERE mhaa=? and mhab=? and mhac in ("+mhacs+")";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().update(sql, new Object[]{mhaa,mhab});
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public int updatePurmh(Purmh purmh) throws DatabaseException {
		String sql = "UPDATE purmh SET mhad=:mhad, mhae=:mhae,mhaf=:mhaf,mhag=:mhag,mhah=:mhah,mhai=:mhai,mhaj=:mhaj, mhak=:mhak,mhal=:mhal,mham=:mham WHERE mhaa=:mhaa and mhab = :mhab and mhac =:mhac";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(purmh);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public int changeStatus(String mhaa, String mhab, String status)throws DatabaseException {
		String sql = "UPDATE purmg SET mgac=? WHERE mgaa=? and mgab = ?";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().update(sql, new Object[]{status,mhaa,mhab});
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public void delPurmhByAaAb(String maaa, String maab) throws DatabaseException {

		String sql2 = "delete from purmh where mhaa = ? and mhab = ?";
		namedParameterJdbcTemplate.getJdbcOperations().update(sql2, new Object[]{maaa,maab});
		
		
	}
}
